package vy.org.app.page.upload;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.vy.dal.DALCtx;
import org.vy.dal.dao.ViewVyLookupPatrikaPeriodDAO;
import org.vy.dal.dao.ViewVyLookupPatrikaPeriodDO;
import org.vy.dal.dao.ViewVyMemberDAO;
import org.vy.dal.dao.ViewVyMemberDO;
import org.vy.dal.dao.VyAddressDAO;
import org.vy.dal.dao.VyAddressDO;
import org.vy.dal.dao.VyMagazineSubscriptionDAO;
import org.vy.dal.dao.VyMagazineSubscriptionDO;
import org.vy.dal.dao.VyMemberDAO;
import org.vy.dal.dao.VyMemberDO;
import org.vy.dal.dao.VyPrintLabelGroupDAO;
import org.vy.dal.dao.VyPrintLabelGroupDO;
import org.vy.dal.dao.lookup.ViewVyLookupCitiesDAO;
import org.vy.dal.dao.lookup.ViewVyLookupCitiesDO;
import org.vy.dal.dao.lookup.VyLookupDeliveryModeDAO;
import org.vy.dal.dao.lookup.VyLookupDeliveryModeDO;
import org.vy.dal.dao.lookup.VyLookupPatrikaDAO;
import org.vy.dal.dao.lookup.VyLookupPatrikaDO;
import org.vy.dal.seq.GroupPrintLabelSeqGenerator;

import vy.org.app.common.AddressTypeEnum;
import vy.org.app.common.SubscriptionIdGenerator;
import vy.org.app.common.VyIdGenerator;
import vy.org.app.page.lookup.patrika.MntnPatrikaPeriodForm;
import vy.org.app.page.lookup.patrika.PatrikaHelper;

public class DataUploader {
	
	private Map<String, Map<String, ViewVyLookupCitiesDO>> cities = new HashMap<String, Map<String, ViewVyLookupCitiesDO>>(); 
	private Map<String, Map<String, ViewVyLookupCitiesDO>> states = new HashMap<String, Map<String, ViewVyLookupCitiesDO>>(); 
	private Map<String, ViewVyLookupCitiesDO> countries = new HashMap<String, ViewVyLookupCitiesDO>();
	private Map<String, List<MntnPatrikaPeriodForm>> patrikaMap = new HashMap<String, List<MntnPatrikaPeriodForm>>();
	//public static SimpleDateFormat _DTFMT = new SimpleDateFormat("MM/dd/yyyy");
	public static SimpleDateFormat _DTFMT = new SimpleDateFormat("dd/MM/yyyy");
	public int success = 0;
	public int failure = 0;
	public int processed = 0;
	private static Calendar cal1990 = Calendar.getInstance();
	private enum OPS {INSERT,UPDATE,VERIFY,IGNORE};
	
	private String err = null;
	private String grpId = null;
	private String prevGrpId = null;
	
	Map<String, VyLookupPatrikaDO> patMap = new HashMap<String, VyLookupPatrikaDO>();
	List<VyLookupDeliveryModeDO> deliveryModeLst = null;
	
	public static void main(String[] s) {
		try{
			String file = "D:\\VYSofts\\NewData\\11\\";
			DataUploader upd = new DataUploader();
			String fmt = _DTFMT.toPattern().replaceAll("/", "-").toLowerCase();
			File dir = new File(file);
			for(File fl : dir.listFiles()){
				if(fl.getAbsolutePath().indexOf("processed") == -1
						&& fl.getAbsolutePath().indexOf(fmt) != -1
						&& fl.getName().endsWith(".csv"))
					upd.upload(fl.getAbsolutePath());
				System.out.println("SUCCESS = "+ upd.success +"  FAILURE = "+ upd.failure +"  PROCESSED = "+ upd.processed);
				upd.success=0;
				upd.failure=0;
				upd.processed=0;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public UploadReport start(int jobId, String beforeFilePath, String afterFilePath) {
		UploadReport rpt = new UploadReport();
		try{
			if(beforeFilePath.endsWith(".csv")){
				upload(jobId, beforeFilePath, afterFilePath);
				System.out.println("SUCCESS = "+ success +"  FAILURE = "+ failure +"  PROCESSED = "+ processed);
				rpt.setStatus(true);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		rpt.setFailure(failure);
		rpt.setProcessed(processed);
		rpt.setSuccess(success);
		return rpt;
	}
	
	public DataUploader() throws Exception{
		
		cal1990.set(1990, 1, 1);
		
		ViewVyLookupCitiesDO dto = new ViewVyLookupCitiesDO();
		dto.setCountryName("India");
		dto.setCountryCode("IN");
		//for(ViewVyLookupCitiesDO dto : lstCountry){
			countries.put(dto.getCountryName().toLowerCase().trim(), dto);
			List<ViewVyLookupCitiesDO> lstStates = ViewVyLookupCitiesDAO.findStateByCountry(dto.getCountryCode());
			Map<String, ViewVyLookupCitiesDO> locState = new HashMap<String, ViewVyLookupCitiesDO>();
			for(ViewVyLookupCitiesDO sto : lstStates){
				locState.put(sto.getStateName().toLowerCase().trim(), sto);
				List<ViewVyLookupCitiesDO> lstCities = ViewVyLookupCitiesDAO.findCitiesByStateAndCountry(sto.getStateCode(), dto.getCountryCode());
				Map<String, ViewVyLookupCitiesDO> locCity = new HashMap<String, ViewVyLookupCitiesDO>();
				for(ViewVyLookupCitiesDO cto : lstCities){
					locCity.put(cto.getCityName().toLowerCase().trim(), cto);
				}
				cities.put(dto.getCountryName().toLowerCase().trim()+"->"+sto.getStateName().toLowerCase().trim(), locCity);
			}
			states.put(dto.getCountryName().toLowerCase().trim(), locState);
		//}
		List<VyLookupPatrikaDO> patrikaLst = VyLookupPatrikaDAO.findAllActive();
		List<ViewVyLookupPatrikaPeriodDO> patPeriodLst = ViewVyLookupPatrikaPeriodDAO.findAllActive();
		deliveryModeLst = VyLookupDeliveryModeDAO.findAllActive();
		
		for(VyLookupPatrikaDO pat : patrikaLst){
			patrikaMap.put(pat.getPatrikaName().toLowerCase(), PatrikaHelper.fillPatrikaPeriodForm(pat, patPeriodLst));
		}
		for(VyLookupPatrikaDO pat : patrikaLst){
			patMap.put(pat.getPatrikaName().toLowerCase(), pat);
		}
	}
	
	private void upload(String file){
		upload(-999, file, file.replaceAll("csv", "processed.csv"));
	}

	private void upload(int jobId, String beforeFile, String afterFile){
		try{
			FileWriter fw = new FileWriter(afterFile);
			BufferedReader br = new BufferedReader(new FileReader(beforeFile));
			String line = null;
			line = br.readLine();
			fw.write(line+"\n");
			line = br.readLine();
			while(line != null){
				try{
					err = null;
					if(!processLine(line, jobId)){
						failure++;
					}else{
						if(err != null) 
							err = "Success " + err;
						else
							err = "Success";
					}
					fw.write(line+"\t"+err+"\n");
					err=null;
				}catch(Exception ep){
					ep.printStackTrace();
					failure++;
					fw.write(line+"\t"+ep.getMessage()+"\n");
					err=null;
				}
				line = br.readLine();
			}
			fw.close();
			br.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	private boolean processLine(String line, int jobId) {
		try{
			String[] parts = line.split("\t"); 
			
			for(int j=0; j < parts.length; j++){
				parts[j] = parts[j].trim();
			}
			
			processed++;
			if("".equals(parts[0])) {
				throw new Exception("Action is missing, it should be either INSERT/UPDATE/VERIFY/IGNORE");
			}
			if(OPS.IGNORE.name().equals(parts[0])){
				return true;
			}else if(OPS.VERIFY.name().equals(parts[0])){
				return verify(parts);
			}else if(OPS.INSERT.name().equals(parts[0])){
				return insert(parts, jobId);
			}else if(OPS.UPDATE.name().equals(parts[0])){
				return update(parts, jobId);
			}
		}catch(Exception e){
			err = e.getMessage();
			e.printStackTrace();
		}
		return false;
	}

	private boolean insert(String[] parts, int jobId){
		DALCtx.ctx().beginTransaction();
		try{
			if(!parts[1].equals(prevGrpId)) {
				prevGrpId = parts[1];
				if(!"".equals(prevGrpId.trim()))
					grpId = GroupPrintLabelSeqGenerator.getInstance().getNextSequence();
				else
					grpId = "";
			}
			String vyId = null;
			String lastUpdBy = "JOB"+jobId;
			VyMemberDO memDO = new VyMemberDO();
			memDO.setFirstName(parts[3]);
			memDO.setMiddleName(parts[4]);
			memDO.setLastName(parts[5]);
			memDO.setRegionalLangName(parts[6]);
			memDO.setPhoneNumber(parts[7]);
			memDO.setLastUpdatedOn(new Date());
			memDO.setLastUpdatedBy(lastUpdBy);
			memDO.setIsUpdestha(false);
			memDO.setIsUpdesh(false);
			if(parts.length > 22)
				memDO.setComments(parts[23]);
			//memDO.setVyId(MemberSeqGenerator.getInstance().getNextSequence());
			memDO.setIsDeleted(false);
			
			List<VyAddressDO> lstAddr = new ArrayList<VyAddressDO>();
			VyAddressDO off=new VyAddressDO(), pmt = new VyAddressDO(), prt = new VyAddressDO();
			lstAddr.add(off);
			lstAddr.add(pmt);
			lstAddr.add(prt);
			
			pmt.setAddressType(AddressTypeEnum.PERMANENT.getName());
			pmt.setIsDeleted(false);
			pmt.setStreet1("");
			pmt.setStateOrRegion("");
			pmt.setCountry("");
			pmt.setPrintLanguage("en");
			pmt.setLastUpdatedBy(lastUpdBy);
			pmt.setLastUpdatedOn(new Date());
			off.setAddressType(AddressTypeEnum.OFFICE.getName());
			off.setIsDeleted(false);
			off.setStreet1("");
			off.setStateOrRegion("");
			off.setCountry("");
			off.setPrintLanguage("en");
			off.setLastUpdatedBy(lastUpdBy);
			off.setLastUpdatedOn(new Date());
			
			prt.setAddressType(AddressTypeEnum.PRESENT.getName());
			prt.setIsDeleted(false);
			prt.setNamePrefix(parts[8].toUpperCase());
			prt.setCoName(parts[9]);
			prt.setStreet1(parts[10]);
			prt.setStreetHindi1(parts[11]);
			prt.setLastUpdatedBy(lastUpdBy);
			prt.setLastUpdatedOn(new Date());
			if(parts[14] == null || "".equals(parts[14]))
				parts[14] = "India";
			prt.setCity(getCity(parts[14].toLowerCase().trim(), parts[13].toLowerCase().trim(), parts[12].toLowerCase().trim()));
			prt.setStateOrRegion(getState(parts[14].toLowerCase().trim(),parts[13].toLowerCase().trim()));
			prt.setCountry(getCountry(parts[14].toLowerCase().trim()));
			prt.setZip(parts[15]);
			if("".equals(parts[11].trim()))
				prt.setPrintLanguage("en");
			else
				prt.setPrintLanguage("hi");
	
			VyMagazineSubscriptionDO mzDO = new VyMagazineSubscriptionDO();
			mzDO.setDeliveryMode(deliveryModeLst.get(0).getDeliveryModeCode());
			mzDO.setIsActive(true);
			try{
				mzDO.setPatrikaId(patMap.get(parts[16].toLowerCase()).getPatrikaId());
			}catch(Exception e){
				throw new Exception("Patrika not found");
			}
			mzDO.setWeekOffsetIndex(patMap.get(parts[16].toLowerCase()).getWeekOffsetIndex());
			mzDO.setQuantity(Integer.parseInt(parts[18]));
			if(!"".equals(parts[22]))
				mzDO.setReceiptDate(_DTFMT.parse(parts[22]));
			mzDO.setReceiptNo(parts[21]);
			mzDO.setLastUpdatedBy("SYSTEM");
			mzDO.setLastUpdatedOn(new Date());
			mzDO.setShippingAmount(0.0);
			mzDO.setPaidAmount(0.0);
			mzDO.setTotalAmount(0.0);
			String startDt = parts[19];
			String endDt = parts[20];
			Date sdt = null;
			Date edt = null;
			
			List<MntnPatrikaPeriodForm> ppForms = patrikaMap.get(parts[16].toLowerCase());
			int month = 0;
			for(MntnPatrikaPeriodForm ppf : ppForms) {
				if(parts[17].equalsIgnoreCase(ppf.getPeriodName())){
					month = ppf.getPeriodMonth();
					mzDO.setPatrikaPeriodId(ppf.getPatrikaPeriodId());
					break;
				}
			}
			if(month == 0){
				throw new Exception("Patrika period not specified");
			}
			if("".equals(startDt) && "".equals(endDt)){
				throw new Exception("Subscription start or end date not specified");
			}else if(!"".equals(startDt) && "".equals(endDt)){
				sdt = _DTFMT.parse(startDt);
				Calendar cal = Calendar.getInstance();
				cal.add(Calendar.MONTH, month);
				edt = cal.getTime();
			}else if("".equals(startDt) && !"".equals(endDt)){
				edt = _DTFMT.parse(endDt);
				Calendar cal = Calendar.getInstance();
				cal.add(Calendar.MONTH, -1*month);
				sdt = cal.getTime();
			}else{
				sdt = _DTFMT.parse(startDt);
				edt = _DTFMT.parse(endDt);
			}
			mzDO.setStartDate(sdt);
			mzDO.setEndDate(edt);
			if(mzDO.getStartDate().before(cal1990.getTime())
					|| mzDO.getEndDate().before(cal1990.getTime()))
				throw new Exception("Incorrect date");
			
			ViewVyMemberDO vym = checkMember(memDO,parts[12].trim(),parts[13].trim(),parts[14].trim(),parts[10].trim(),parts[11].trim());
			boolean isNewMember = true;
			
			if(vym == null || vym.getVyId() == null){
				vyId = VyIdGenerator.generateId(memDO, lstAddr);

				prt.setRefId(vyId);
				off.setRefId(vyId);
				pmt.setRefId(vyId);
				off = VyAddressDAO.insert(off);
				prt = VyAddressDAO.insert(prt);
				pmt = VyAddressDAO.insert(pmt);
				
				memDO.setVyId(vyId);
				memDO.setPermanentAddressId(pmt.getAddressId());
				memDO.setOfficeAddressId(off.getAddressId());
				memDO.setPresentAddressId(prt.getAddressId());
				
				VyMemberDAO.insert(memDO);
				
				mzDO.setAddressId(prt.getAddressId().toString());
				mzDO.setVyId(memDO.getVyId());
			} else{
				vyId = vym.getVyId();
				memDO.setVyId(vyId);
				mzDO.setAddressId(vym.getPrtAddressId().toString());
				mzDO.setVyId(vyId);
				isNewMember = false;
			}
			
			VyMagazineSubscriptionDO subs = null;
			if(!isNewMember) subs = checkSubscription(mzDO);
			
			if(subs == null || subs.getSubscriptionId() == null) {
				mzDO.setSubscriptionId(SubscriptionIdGenerator.generateId(mzDO, mzDO.getReceiptDate(), prt.getCountry(), prt.getStateOrRegion()));
				VyMagazineSubscriptionDAO.insert(mzDO);
				
				if(null != grpId && !"".equals(grpId)){
					VyPrintLabelGroupDO pgl = new VyPrintLabelGroupDO();
					if("Y".equalsIgnoreCase(parts[2].toUpperCase().trim())
							||"YES".equalsIgnoreCase(parts[2].toUpperCase().trim())
							||"TRUE".equalsIgnoreCase(parts[2].toUpperCase().trim()))
						pgl.setIsLabelAddress(true);
					else
						pgl.setIsLabelAddress(false);
					pgl.setPrintLabelGroupId(grpId);
					pgl.setSubscriptionId(mzDO.getSubscriptionId());
					pgl.setVyId(vyId);
					VyPrintLabelGroupDAO.insert(pgl);
				}
				err = "VY_ID=" + vyId +" SUBSCRIPTION_ID="+ mzDO.getSubscriptionId();
			}else{
				err = "Subscription already exist VY_ID=" + vyId +" SUBSCRIPTION_ID="+ subs.getSubscriptionId();
				DALCtx.ctx().rollbackTransaction();
				return false;
			}
			DALCtx.ctx().commitTransaction();
			success++;
			return true;
			
		}catch(Exception e){
			err = e.getMessage();
			e.printStackTrace();
			DALCtx.ctx().rollbackTransaction();
		}
		return false;
	}
	
	private boolean verify(String[] parts) throws Exception{
		throw new Exception("Operation not supported");
	}
	
	private boolean update(String[] parts, int jobId) throws Exception{
		throw new Exception("Operation not supported");
	}

	private String getCity(String country, String state, String city) throws Exception{
		if(cities.containsKey(country+"->"+state)){
			Map<String, ViewVyLookupCitiesDO> map = cities.get(country+"->"+state);
			ViewVyLookupCitiesDO dto = map.get(city);
			if(dto == null) throw new Exception("City not found :: "+ country +"->"+state+"->"+city);
			return dto.getCityCode();
		}else{
			throw new Exception("City not found :: "+ country +"->"+state+"->"+city);
		}
	}

	private String getState(String country, String state) throws Exception{
		if(states.containsKey(country)){
			Map<String, ViewVyLookupCitiesDO> map = states.get(country);
			ViewVyLookupCitiesDO dto = map.get(state);
			if(dto == null)  throw new Exception("State not found :: "+ country +"->"+state);
			return dto.getStateCode();
		}else
			 throw new Exception("State not found :: "+ country +"->"+state);
	}

	private String getCountry(String country) throws Exception{
		if(countries.containsKey(country)){
			ViewVyLookupCitiesDO dto = countries.get(country);
			if(dto == null) throw new Exception("Country not found :: "+ country);
			return dto.getCountryCode();
		}else
			throw new Exception("Country not found :: "+ country);
	}
	
	private VyMagazineSubscriptionDO checkSubscription(VyMagazineSubscriptionDO mzDO){
		VyMagazineSubscriptionDO vyms = null;
		List<VyMagazineSubscriptionDO> lstVYMS = null;
		
		String where = " 1=1 ";
		if(mzDO.getVyId() != null && !"".equals(mzDO.getVyId()))
			where += " AND VY_ID = '"+ mzDO.getVyId() +"' ";
		if(mzDO.getPatrikaId() != null && !"".equals(mzDO.getPatrikaId()))
			where += " AND PATRIKA_ID = '"+ mzDO.getPatrikaId() +"' ";
		if(mzDO.getPatrikaPeriodId() != null && !"".equals(mzDO.getPatrikaPeriodId()))
			where += " AND PATRIKA_PERIOD_ID = '"+ mzDO.getPatrikaPeriodId() +"' ";
		if(mzDO.getStartDate() != null)
			where += " AND DATE_FORMAT(START_DATE,'%d/%m/%Y') = '"+ getSQLString(mzDO.getStartDate()) +"' ";
		if(mzDO.getEndDate() != null)
			where += " AND DATE_FORMAT(END_DATE,'%d/%m/%Y') = '"+ getSQLString(mzDO.getEndDate()) +"' ";
		
		try{
			lstVYMS = (List<VyMagazineSubscriptionDO>)VyMagazineSubscriptionDAO.findByParam(where);
			if(lstVYMS != null && lstVYMS.size() > 0)
				vyms = lstVYMS.get(0);
		}catch(Exception e){
			e.printStackTrace();
		}		
		
		return vyms;
	}
	
	private ViewVyMemberDO checkMember(VyMemberDO mem, String city, String state, String country, String street1, String hindiStreet1){
		ViewVyMemberDO vym = null;
		List<ViewVyMemberDO> lstVYM = null;
		
		String where = " 1=1 ";
		if(mem.getFirstName() != null && !"".equals(mem.getFirstName().trim()))
			where += " AND FIRST_NAME = '"+ getSQLString(mem.getFirstName()) +"' ";
		if(mem.getMiddleName() != null && !"".equals(mem.getMiddleName().trim()))
			where += " AND MIDDLE_NAME = '"+ getSQLString(mem.getMiddleName()) +"' ";
		if(mem.getLastName() != null && !"".equals(mem.getLastName().trim()))
			where += " AND LAST_NAME = '"+ getSQLString(mem.getLastName()) +"' ";
		if(mem.getRegionalLangName() != null && !"".equals(mem.getRegionalLangName().trim()))
			where += " AND REGIONAL_LANG_NAME = '"+ getSQLString(mem.getRegionalLangName()) +"' ";
		
		if(city != null && !"".equals(city.trim())){
			where += " AND (PRT_CITY = '"+ getSQLString(city) +"' ";
			where += " OR PMT_CITY = '"+ getSQLString(city) +"' ";
			where += " OR OFF_CITY = '"+ getSQLString(city) +"') ";
		}
		if(state != null && !"".equals(state.trim())){
			where += " AND (PRT_STATE_OR_REGION = '"+ getSQLString(state) +"' ";
			where += " OR PMT_STATE_OR_REGION = '"+ getSQLString(state) +"' ";
			where += " OR OFF_STATE_OR_REGION = '"+ getSQLString(state) +"') ";
		}
		if(country != null && !"".equals(country.trim())){
			where += " AND (PRT_COUNTRY = '"+ getSQLString(country) +"' ";
			where += " OR PMT_COUNTRY = '"+ getSQLString(country) +"' ";
			where += " OR OFF_COUNTRY = '"+ getSQLString(country) +"') ";
		}
		if(street1 != null && !"".equals(street1.trim())){
			where += " AND (PRT_STREET_1 = '"+ getSQLString(street1) +"' ";
			where += " OR PMT_STREET_1 = '"+ getSQLString(street1) +"' ";
			where += " OR OFF_STREET_1 = '"+ getSQLString(street1) +"') ";
		}
		if(hindiStreet1 != null && !"".equals(hindiStreet1.trim())){
			where += " AND (PRT_STREET_HINDI_1 = '"+ getSQLString(hindiStreet1) +"' ";
			where += " OR PMT_STREET_HINDI_1 = '"+ getSQLString(hindiStreet1) +"' ";
			where += " OR OFF_STREET_HINDI_1 = '"+ getSQLString(hindiStreet1) +"') ";
		}

		try{
			lstVYM = (List<ViewVyMemberDO>)ViewVyMemberDAO.findByParam(where);
			if(lstVYM != null && lstVYM.size() > 0)
				vym = lstVYM.get(0);
		}catch(Exception e){
			e.printStackTrace();
		}
		return vym;
	}

	private static String getSQLString(Object value){
		if(value == null) return "NULL";
		
		if(value instanceof String){
			return value.toString().replaceAll("'","''"); 
		}else if(value instanceof Integer
				|| value instanceof Long
				|| value instanceof Float
				|| value instanceof Double
				|| value instanceof BigInteger){
			return value.toString();
		}else if(value instanceof java.util.Date){
			return _DTFMT.format((java.util.Date)value);
		}
		return value.toString();
	}
}
